﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
	public DataAccess()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static SqlConnection Tao_ket_noi()
    {
        SqlConnection con = null;
        try
        {
            string Chuoi_ket_noi = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            string cms = ConfigurationManager.ConnectionStrings["constringCMS"].ConnectionString;
            con = new SqlConnection(Chuoi_ket_noi);
        }
        catch (SqlException ex)
        {
            con = null;
            throw new Exception(ex.Message);
        }
        return con;
    }
    public static DataTable DataTable_Query(string query)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlConnection con = Tao_ket_noi();
            SqlDataAdapter dap = new SqlDataAdapter(query, con);
            dap.Fill(dt);
        }
        catch (SqlException ex)
        {
            return null;
            throw new Exception(ex.Message);
        }
        return dt;
    }

    public static DataTable DataTable_Procedure(string procedurename, string[] paras, string[] values)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlConnection con = Tao_ket_noi();
            SqlCommand com = new SqlCommand(procedurename, con);
            com.Connection = con;
            com.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < paras.Length; i++)
            {
                com.Parameters.AddWithValue(paras[i], values[i]);
            }
            SqlDataAdapter dap = new SqlDataAdapter(com);
            dap.Fill(dt);

        }
        catch (SqlException ex)
        {
            dt = null;
            throw new Exception(ex.Message);
        }
        return dt;
    }
    
    public static SqlDataReader DataReader_getReader(string query)
    {
        try
        {
            SqlConnection con = Tao_ket_noi();
            con.Open();
            SqlCommand com = new SqlCommand(query, con);
            return com.ExecuteReader();
            con.Close();
        }
        catch (SqlException ex)
        {
            return null;
            throw new Exception(ex.Message);
        }

    }

    public static SqlDataReader getReaderProcedure(string procedurename, string[] paras, string[] values)
    {
        try
        {
            SqlConnection con = Tao_ket_noi();
            SqlCommand com = new SqlCommand(procedurename, con);
            con.Open();
            com.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < paras.Length; i++)
            {
                com.Parameters.AddWithValue(paras[i], values[i]);
            }
            return com.ExecuteReader();
            con.Close();
        }
        catch (SqlException ex)
        {
            return null;
            throw new Exception(ex.Message);
        }
    }

    
    //hàm lấy giá trị trả về là kết quả đơn vD:hàm Count
    public static object getScalar(string query)
    {
        try
        {
            SqlConnection con = Tao_ket_noi();
            con.Open();
            SqlCommand com = new SqlCommand(query, con);
            return com.ExecuteScalar();
            con.Close();
        }
        catch (SqlException ex)
        {
            return null;
            throw new Exception(ex.Message);
        }
    }
    public static int getExec(string query)
    {
        int result = 0;
        try
        {
            SqlConnection con = Tao_ket_noi();
            con.Open();
            SqlCommand com = new SqlCommand(query, con);
            result = com.ExecuteNonQuery();
            con.Close();
        }
        catch (SqlException ex)
        {
            return result = 0;
            throw new Exception(ex.Message);
        }

        return result;
    }

    public static int getExecProcedure(string procedurename, string[] paras, string[] values)
    {
        int result = 0;
        try
        {
            SqlConnection con = Tao_ket_noi();
            SqlCommand com = new SqlCommand(procedurename, con);
            con.Open();
            com.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < paras.Length; i++)
            {
                com.Parameters.AddWithValue(paras[i],values[i]);
                string t=com.ToString();
            }
            result = com.ExecuteNonQuery();
            con.Close();
        }
        catch (SqlException ex)
        {
            return result = 0;
            throw new Exception(ex.Message);
        }
        return result;
    }
    public static int ImageProcedure(string procedureName, string[] para, string[] value, string paramaterImageName, byte[] contentImage)
    {
        int result = 0;
        try
        {
            SqlConnection con = Tao_ket_noi();
            SqlCommand com = new SqlCommand(procedureName, con);
            con.Open();
            com.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < para.Length; i++)
            {
                //if (isImage)//image
                //{
                if (para[i].Equals(paramaterImageName))
                {
                    com.Parameters.Add(paramaterImageName, SqlDbType.Image, contentImage.Length).Value = contentImage;
                }
                else
                {
                    com.Parameters.Add(para[i], SqlDbType.NVarChar, value[i].Length).Value = value[i];
                }
                //}
                //else//no image
                //{
                //    if (!para[i].Equals(paramaterImageName))
                //    {
                //        com.Parameters.Add(para[i], SqlDbType.NVarChar, value[i].Length).Value = value[i];
                //    }
                //}
            }
            result = com.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            result = 0;
            throw new Exception(ex.Message);
        }
        return result;
    }
}